/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 * ---------------------------------------------------
 * This class is the gate for receiving all transmitted data from all channels
 * like: internet, bluetooth, wi-fi, compying file...
 * 
 */
package DbPort;

import DataTypes.AutoDiaryDataTypes;
import DataTypes.AutoDiaryDataTypes.ActLog;
import DataTypes.AutoDiaryDataTypes.AmbiAct;
import DataTypes.AutoDiaryDataTypes.AmbiLiteral;
import DataTypes.AutoDiaryDataTypes.ClearActLog;
import DataTypes.AutoDiaryDataTypes.Gps;
import DataTypes.AutoDiaryDataTypes.PlaceLog;
import DataTypes.AutoDiaryDataTypes.PrimaryActivity;
import DataTypes.AutoDiaryDataTypes.WeightMatrix;
import DataTypes.GoogleApiDataTypes;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author thang
 */
public class DataReceiver {
    
    /*
     * receive reasoning result (answer set) and insert into d_actLog
     * - input: answer set at time t
     * if whole anser set are the same then just update t2, otherwise, insert new records
     * - two answer set are the same if having the same: literal set and device_id, all the others can be different
     * - at the moment suppose there only one answer set at a time
     * - If ther are more than one, then input will be a list of WeightMatrix (each one is one answer set)
     * - table d_actLog will need another culumn (answerSet_id) to distinguigh them
     * ----------------------------------------------------
     * REMEMBER, GPS DATA NEED TO BE STORED IN D_GPSLOG IN SIMILAR WAY
     * ----------------------------------------------------
     */

    /*
     * log user's known places
     */
    
    public static void receiveClearActLog(List<AutoDiaryDataTypes.ClearActLog> cList){ 
        // check null
        if( cList == null || cList.isEmpty() ){ return; }
        
        // get latest answer set from log
        List<AutoDiaryDataTypes.ClearActLog> lastSet = getLastClearActs(autodiaryserver.Declare.DEVICE_ID, cList.get(0).time2_int);

        // if different with the new one then insert

        if ( !sameClearActSet(cList, lastSet) ){
            // insert
            insertClearActSet(cList);
        } else {
            // update
            updateClearActSet(cList.get(0).time2_int, lastSet);
        }
    }

    /*
     * update the time2 of the current/latest set of one device/user.
     */
    public static void updateClearActSet( int time, List<ClearActLog> set){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        if ( set.isEmpty() ) { return; }
        
        //int time_int = Integer.parseInt( (time + "").trim().substring(0, 10) );
        Statement st = null;
        
        try {
            // get list of ids of literals of current set for update
            String idList = "";
            for( ClearActLog a: set ){
                idList += a.id + ",";
            }
            
            // cut lat ","
            idList = idList.substring(0, idList.length() - 1);
            
            // can't use prepare statement with idList
            String sqlCommand = "update d_clearActLog set time2_int = " + time + " where id in (" + idList + ") and device_id = '" + autodiaryserver.Declare.DEVICE_ID + "'";
            st = Declare.mCon.createStatement();
            st.executeUpdate(sqlCommand);
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
    }
    
    public static void insertClearActSet( List<ClearActLog> newSet ){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        PreparedStatement st = null;

        try {
            for( ClearActLog c: newSet ){
            
                st = Declare.mCon.prepareStatement("INSERT INTO d_clearActLog(device_id, code, time1_int, time2_int) "
                        + "VALUES(?, ?, ?, ?)" );

                st.setString(1, c.device_id);
                st.setString(2, c.code );
                st.setInt(3, c.time1_int );
                st.setInt(4, c.time2_int );
                
                st.executeUpdate();
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
    }
    
    public static List<AutoDiaryDataTypes.ClearActLog> getLastClearActs(String device_id, int time){
        List<AutoDiaryDataTypes.ClearActLog> result = null;
        
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            
            st = Declare.mCon.prepareStatement("SELECT id, device_id, code, time1_int, time2_int "
                    + "FROM d_clearActLog WHERE device_id = ? and time2_int = "
                    + "(SELECT max(time2_int) FROM d_clearActLog WHERE Device_id = ?)" );
            st.setString(1, device_id);
            st.setString(2, device_id);
            
            rs = st.executeQuery();
            
            System.out.println(" and Good here ...");
            
            if ( rs.first() ){
                result = new ArrayList<AutoDiaryDataTypes.ClearActLog>();
                while ( !rs.isAfterLast() ){
                    AutoDiaryDataTypes.ClearActLog ca = new AutoDiaryDataTypes.ClearActLog();
                    
                    ca.id = rs.getLong(1);
                    ca.device_id = rs.getString(2);
                    ca.code = rs.getString(3);
                    ca.time1_int = rs.getInt(4);
                    ca.time2_int = rs.getInt(5);
                   
                    result.add( ca );
                    rs.next();
                }
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
        return result;
    }

    /*
     * Compare new answer set with the lastest one
     * - two answer set are the same if having the same: literal set and device_id, all the others can be different
     */
    public static boolean sameClearActSet(List<AutoDiaryDataTypes.ClearActLog> cList, List<AutoDiaryDataTypes.ClearActLog> lastSet){
        boolean result = false;
        
        // check null
        if ( cList == null || lastSet == null || cList.isEmpty() || lastSet.isEmpty() ){ return false;}
        //check device_id
        if ( !(cList.get(0).device_id.equalsIgnoreCase( lastSet.get(0).device_id )) ){ return false;}
        //check equal size
        if ( cList.size() != lastSet.size() ) { return false;}
        
        // copyLastSet for safe as will be delete later when comparing
        ArrayList<AutoDiaryDataTypes.ClearActLog> cpSet = new ArrayList<AutoDiaryDataTypes.ClearActLog>();
        copyClearActSet(lastSet, cpSet);
        
        // compare two way to be sure.
        for( AutoDiaryDataTypes.ClearActLog c: cList ){
            boolean found = false;
            
            for( int i = 0; i < cpSet.size(); i++ ){
                if( c.code.equalsIgnoreCase(cpSet.get(i).code) ) { 
                    found = true; 
                    cpSet.remove(i);
                    break;
                }
            }
            
            if ( !found ){ return false; }
        }
        
        // if equal
        if( cpSet.isEmpty() ){ return true; }
        
        return result;
    }    

    
    // copy from lastSet to copy
    public static void copyClearActSet(List<ClearActLog> lastSet, List<ClearActLog> copySet){
        if ( lastSet == null || lastSet.isEmpty() ){ copySet = null; return; }

        for( ClearActLog c: lastSet ){
            ClearActLog cp = new ClearActLog();
            cp.id = c.id;
            cp.device_id = c.device_id;
            cp.code = c.code;
            cp.time1_int = c.time1_int;
            cp.time2_int = c.time2_int;

            copySet.add(cp);
        }
    }
    
    
    /*
     * log user's known places
     */
    
    public static void receiveData(List<AutoDiaryDataTypes.PlaceLog> pList){
        // check null
        if( pList == null || pList.isEmpty() ){ return; }
        
        // get latest answer set from log
        List<PlaceLog> lastSet = getLastKnownPlaces(autodiaryserver.Declare.DEVICE_ID, pList.get(0).time2_int );

        // if different with the new one then insert

        if ( !sameSet(pList, lastSet) ){
            // insert
            insertSet(pList);
        } else {
            // update
            updateLastKownPlaces(pList.get(0).time2_int, lastSet);
        }
    }

    /*
     * update the time2 of the current/latest set of one device/user.
     */
    public static void updateLastKownPlaces( int time, List<PlaceLog> set){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        if ( set.isEmpty() ) { return; }
        
        //int time_int = Integer.parseInt( (time + "").trim().substring(0, 10) );
        Statement st = null;
        
        try {
            // get list of ids of literals of current set for update
            String idList = "";
            for( PlaceLog a: set ){
                idList += a.id + ",";
            }
            
            // cut lat ","
            idList = idList.substring(0, idList.length() - 1);
            
            // can't use prepare statement with idList
            String sqlCommand = "update d_placeLog set time2_int = " + time + " where id in (" + idList + ") and device_id = '" + autodiaryserver.Declare.DEVICE_ID + "'";
            st = Declare.mCon.createStatement();
            st.executeUpdate(sqlCommand);
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
    }
    
    
    /*
     * Compare new place set with the lastest one
     * - two answer set are the same if having the same: literal set and device_id, all the others can be different
     */
    public static boolean sameSet(List<AutoDiaryDataTypes.PlaceLog> pList, List<AutoDiaryDataTypes.PlaceLog> lastSet){
        boolean result = false;
        
        // check null
        if ( pList == null || lastSet == null || pList.isEmpty() || lastSet.isEmpty() ){ return false;}
        //check device_id
        if ( !(pList.get(0).device_id.equalsIgnoreCase( lastSet.get(0).device_id )) ){ return false;}
        //check equal size
        if ( pList.size() != lastSet.size() ) { return false;}
        
        // copyLastSet for safe as will be delete later when comparing
        ArrayList<PlaceLog> cpSet = new ArrayList<PlaceLog>();
        copyPlaceSet(lastSet, cpSet);
        
        // compare two way to be sure.
        for( PlaceLog p: pList ){
            boolean found = false;
            
            for( int i = 0; i < cpSet.size(); i++ ){
                if( p.type_id == cpSet.get(i).type_id ) { 
                    found = true; 
                    cpSet.remove(i);
                    break;
                }
            }
            
            if ( !found ){ return false; }
        }
        
        // if equal
        if( cpSet.isEmpty() ){ return true; }
        
        return result;
    }
    
    // copy from lastSet to copy
    public static void copyPlaceSet(List<PlaceLog> lastSet, List<PlaceLog> copySet){
        if ( lastSet == null || lastSet.isEmpty() ){ copySet = null; return; }

        for( PlaceLog p: lastSet ){
            PlaceLog cp = new PlaceLog();
            cp.id = p.id;
            cp.device_id = p.device_id;
            cp.type_id = p.type_id;
            cp.time1_int = p.time1_int;
            cp.time2_int = p.time2_int;

            copySet.add(cp);
        }

    }
    
    public static List<PlaceLog> getLastKnownPlaces(String device_id, int time){
        List<PlaceLog> result = null;
        
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            
            System.out.println("Good here ...");
            
            st = Declare.mCon.prepareStatement("SELECT id, device_id, type_id, time1_int, time2_int "
                    + "FROM d_placeLog WHERE device_id = ? and time2_int = "
                    + "(SELECT max(time2_int) FROM d_placeLog WHERE Device_id = ?)" );
            st.setString(1, device_id);
            st.setString(2, device_id);
            
            rs = st.executeQuery();
            
            System.out.println(" and Good here ...");
            
            if ( rs.first() ){
                result = new ArrayList<PlaceLog>();
                while ( !rs.isAfterLast() ){
                    PlaceLog kp = new PlaceLog();
                    
                    kp.id = rs.getLong(1);
                    kp.device_id = rs.getString(2);
                    kp.type_id = rs.getInt(3);
                    kp.time1_int = rs.getInt(4);
                    kp.time2_int = rs.getInt(5);
                   
                    result.add( kp );
                    rs.next();
                }
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
        return result;
    }
    
    public static void receiveData(WeightMatrix actList){
        // check null
        if( actList == null || actList.matrix == null || actList.matrix.isEmpty() ){ return; }
        
        // get latest answer set from log
        List<ActLog> lastSet = getLastSet(autodiaryserver.Declare.DEVICE_ID, actList.time);
        // if different with the new one then insert
        if ( !sameSet(actList, lastSet) ){
            // insert
            insertSet(actList);
        } else {
            // update
            updateSet(actList.time, lastSet);
        }
    }
    
    public static void insertSet( List<PlaceLog> newSet ){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        PreparedStatement st = null;

        try {
            for( PlaceLog p: newSet ){
            
                st = Declare.mCon.prepareStatement("insert into d_placeLog(device_id, type_id, time1_int, time2_int) "
                        + "values(?, ?, ?, ?)" );

                st.setString(1, p.device_id);
                st.setInt(2, p.type_id );
                st.setInt(3, p.time1_int );
                st.setInt(4, p.time2_int );
                
                st.executeUpdate();
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
    }

    public static void insertSet( WeightMatrix newSet ){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        int time_int = Integer.parseInt( (newSet.time + "").trim().substring(0, 10) );
        PreparedStatement st = null;

        try {
            for( AmbiLiteral a: newSet.matrix ){
            
                st = Declare.mCon.prepareStatement("insert into d_actLog(device_id, code, time1, time2, certainty, time2_int) values(?, ?, ?, ?, ?, ?)" );

                st.setString(1, newSet.device_id );
                st.setString(2, a.code );
                st.setLong(3, newSet.time );
                st.setLong(4, newSet.time );
                st.setDouble(5, a.memberValue );
                st.setInt(6, time_int );
                
                st.executeUpdate();
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
    }
    
    /*
     * update the time2 of the current/latest set of one device/user.
     */
    public static void updateSet( Long time, List<ActLog> set){
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        if ( set.isEmpty() ) { return; }
        
        int time_int = Integer.parseInt( (time + "").trim().substring(0, 10) );
        Statement st = null;
        
        try {
            // get list of ids of literals of current set for update
            String idList = "";
            for( ActLog a: set ){
                idList += a.id + ",";
            }
            
            // cut lat ","
            idList = idList.substring(0, idList.length() - 1);
            
            // can't use prepare statement with idList
            String sqlCommand = "update d_actLog set time2 = " + time + " where id in (" + idList + ") and device_id = '" + autodiaryserver.Declare.DEVICE_ID + "'";
            st = Declare.mCon.createStatement();
            st.executeUpdate(sqlCommand);
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
    }
    
    /*
     * Compare new answer set with the lastest one
     * - two answer set are the same if having the same: literal set and device_id, all the others can be different
     */
    public static boolean sameSet(WeightMatrix actList, List<ActLog> lastSet){
        boolean result = false;
        
        if ( actList == null || lastSet == null ){ return false;}
        
        if ( !(actList.device_id.equalsIgnoreCase(lastSet.get(0).device_id)) ){ return false;}
        
        if ( actList.matrix.size() != lastSet.size() ) { return false;}
        
        // copyLastSet for safe as will be delete later when comparing
        ArrayList<ActLog> cpSet = new ArrayList<ActLog>();
        copyLastSet(lastSet, cpSet);
        
        // compare two way to be sure.
        for( AmbiLiteral a: actList.matrix ){
            boolean found = false;
            
            for( int i = 0; i < cpSet.size(); i++ ){
                if( a.code.equalsIgnoreCase(cpSet.get(i).code) ) { 
                    found = true; 
                    cpSet.remove(i);
                    break;
                }
            }
            
            if ( !found ){ return false; }
        }
        
        // if equal
        if( cpSet.isEmpty() ){ return true; }
        
        return result;
    }
    
    // copy from lastSet to copy
    public static void copyLastSet(List<ActLog> lastSet, List<ActLog> copySet){
        if ( lastSet == null ){ copySet = null; return; }
        for( ActLog a: lastSet ){
            ActLog ca = new ActLog();
            ca.id = a.id;
            ca.device_id = a.device_id;
            ca.code = a.code;
            ca.time1 = a.time1;
            ca.time2 = a.time2;
            ca.certainty = a.certainty;
            copySet.add(ca);
        }
    }
    
   
    /*
     * get latest anser set from a device with time less than a cerntain time
     * This can be used to insert the answer set which is not the latest one.
     * need to be fix becase "Where ... time2 = (select max(time2)" is not corrrect
     */
    public static List<ActLog> getLastSet(String device_id, long maxTime){
        List<ActLog> result = null;
        
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            st = Declare.mCon.prepareStatement("select id, device_id, code, time1, time2, certainty from d_actLog "
                    + "where device_id = ? and time2 < ? and time2 = (select max(time2) from d_actLog where Device_id = ?)" );
            st.setString(1, device_id);
            st.setLong(2, maxTime);
            st.setString(3, device_id);
            
            rs = st.executeQuery();
            if ( rs.first() ){
                result = new ArrayList<ActLog>();
                while ( !rs.isAfterLast() ){
                    ActLog al = new ActLog();
                    
                    al.id = rs.getLong(1);
                    al.device_id = rs.getString(2);
                    al.code = rs.getString(3);
                    al.time1 = rs.getLong(4);
                    al.time2 = rs.getLong(5);
                    al.certainty = rs.getDouble(6);
                    
                    result.add( al );
                    rs.next();
                }
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
       
        return result;
    }
    
    /*
     * receive event and insert into datawindow
     */
    public static boolean receiveData(AmbiAct aa){
        boolean result = false;
        // convert time from millisecond to second
        // from long to int
        // this is correct for next 94 years.
        int time_int = Integer.parseInt( (aa.getSensorTimeStamp() + "").trim().substring(0, 10) );
        PreparedStatement st = null;

        try {
            st = Declare.mCon.prepareStatement("INSERT INTO d_actWindow (device_id, code, time, time_int, gps_id, place_id, weight, level, window_index) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?);" );

            st.setString(1, aa.getDevice_id() );
            st.setString(2, aa.getActivityCode() );
            st.setLong(3, aa.getSensorTimeStamp() );
            st.setInt(4, time_int );
            st.setDouble(5, aa.getGpsId() );
            st.setDouble(6, aa.getPlaceId() );
            st.setInt(7, aa.getWeight() );
            st.setInt(8, aa.getLevel() );
            st.setInt(9, aa.getWindowIndex() );
            
            st.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
        return result;
    }

     
    /*
     * receive primary activity and insert into database
     * return true if can insert into db
     */
    public static boolean receiveData(PrimaryActivity pa){
        boolean result = false;
        PreparedStatement st = null;
        try {
            // convert time from millisecond to second
            // from long to int
            // this is correct for next 94 years.
            int time_int = Integer.parseInt( (pa.getSensorTimeStamp() + "").trim().substring(0, 10) );
            
            st = Declare.mCon.prepareStatement("INSERT INTO d_primActivities (device_id, code, time, time_int) VALUES(?, ?, ?, ?);" );

            st.setString( 1, pa.getDevice_id() );
            st.setString(2, pa.getActivityCode());
            st.setLong(3, pa.getSensorTimeStamp());
            st.setInt(4, time_int);
            
            st.executeUpdate();
            
            result = true;
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
        
        return result;        
    }
    
    /*
     * receive primary activity and insert into database
     * return gpsData id, -1 if error
     */
    public static long receiveData(Gps g){
        long newId = -1;
        PreparedStatement ps = null;

        int accuracy_int = Math.round( g.getGpsAccuracy() );
        int speed_int = Math.round( g.getGpsSpeed() );
        int time_int = Integer.parseInt( (g.getGpsTime() + "").substring(0, 10) );

        try {            
            // this block should lock Db
            
            // get id to insert places aswell
            ResultSet rs = Declare.mCon.prepareStatement("select max(id) as max_id from d_gpsData;").executeQuery();
            
            if (rs.first()){
                newId = rs.getLong(1) + 1;
            }else{
                newId = 0;
            }
            
            ps = Declare.mCon.prepareStatement("insert into d_gpsData (id, latitude, longitude, accuracy, speed, time, accuracy_int, speed_int, time_int, device_id) values(?,?,?,?,?,?,?,?,?,?)");
            
            ps.setLong(1, newId);
            ps.setDouble(2, g.getGpsLat());
            ps.setDouble(3, g.getGpsLong());
            ps.setFloat(4, g.getGpsAccuracy());
            ps.setFloat(5, g.getGpsSpeed());
            ps.setLong(6, g.getGpsTime());
            ps.setInt(7, accuracy_int);
            ps.setInt(8, speed_int);
            ps.setInt(9, time_int);
            ps.setString(10, g.getDevice_id() );
            
            ps.executeUpdate();
            
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( ps != null ){ try {  ps.close(); } catch (SQLException ex) {} }
        }
        
        return newId;
    }
    
    /*
     * insert place into db and return placeId
     */
    public static long receiveData(DataTypes.AutoDiaryDataTypes.Place p){
        long newId = -1;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try {
            
            // get id to insert into placesTypes aswell
            // need to lock DB to insert then unlock DB !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            rs = Declare.mCon.prepareStatement("select max(id) as max_id from d_places;").executeQuery();
            if (rs.first()){
                newId = rs.getLong(1) + 1;
            } else {  newId = 0;     }
            rs.close();
            rs = null;

            // insert into table d_places
            ps = Declare.mCon.prepareStatement("insert into d_places (id, name, distance, gpsData_id, latitude, longitude) values(?,?,?,?,?,?)");
            ps.setLong(1, newId);
            ps.setString(2, p.getName());
            ps.setInt(3, p.getDistance());
            ps.setLong(4, p.getGpsDataId());
            ps.setDouble(5, p.getLatitude());
            ps.setDouble(6, p.getLongitude());

            ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{ 
            if (rs != null) { try { rs.close(); } catch (SQLException ex) {}}  
            if (ps != null) { try { ps.close(); } catch (SQLException ex) {}}  
        }
        
        return newId;        
    }
    
    /*
     * return ID for the type just inserted
     */
    public static int receiveData(DataTypes.AutoDiaryDataTypes.Type t){
        
        int newId = -1;
        ResultSet rs = null;
        PreparedStatement ps = null;
        
        try {        
            // get id to insert into d_types 
            // need to lock DB to insert then unlock DB !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            rs = Declare.mCon.prepareStatement("select max(id) as max_id from d_pTypes;").executeQuery();
            if (rs.first()){
                newId = rs.getInt(1) + 1;
            } else {  newId = 0;     }
            rs.close();
            rs = null;

            // insert into table d_places
            ps = Declare.mCon.prepareStatement("insert into d_pTypes (id, type) values(?, ?);");
            ps.setInt(1, newId);
            ps.setString(2, t.getType());

            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{ 
            if (rs != null) { try { rs.close(); } catch (SQLException ex) {}}  
            if (ps != null) { try { ps.close(); } catch (SQLException ex) {}}  
        }
        
        return newId;
    }
    
    public static void receivePlacesTypes(long placeId, int typeId){
        PreparedStatement ps = null;
        
        try {        
            // insert into table d_placesTypes
            ps = Declare.mCon.prepareStatement("insert into d_placesTypes (place_id, type_id) values(?, ?);");
            ps.setLong(1, placeId);
            ps.setInt(2, typeId);

            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{ 
            if (ps != null) { try { ps.close(); } catch (SQLException ex) {}}  
        }
        
    }
    
    
    /*
     * look up id of a type
     * reurn -1 if not found
     */
    public static int getDTypesID(String type){
        int id = -1;
        ResultSet rs = null;
        PreparedStatement ps = null;

        try {        
            // get type id 
            ps = Declare.mCon.prepareStatement("select id from d_pTypes where type = ?");
            ps.setString(1, type.trim());
            rs = ps.executeQuery();
            
            if (rs.first()){
                id = rs.getInt(1);
            }

        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{ 
            if (rs != null) { try { rs.close(); } catch (SQLException ex) {}}  
            if (ps != null) { try { ps.close(); } catch (SQLException ex) {}}  
        }

        return id;
    }
    
    /*
     * update address with a gpsData_id or place_id
     * which = 1 then update gpsData_id
     * which = 2 then update place_id
     * 
     */
    public static void updateAddress(long gpsData_id, long place_id, String which) {
        PreparedStatement ps = null;

        try {        
            
            if (which.equalsIgnoreCase("place_id") ){
                ps = Declare.mCon.prepareStatement("UPDATE d_address SET place_id=? WHERE gpsData_id=?");
                ps.setLong(1, place_id);
                ps.setLong(2, gpsData_id);
            } else if (which.equalsIgnoreCase("gpsData_id") ){
                ps = Declare.mCon.prepareStatement("UPDATE d_address SET gpsData_id=? WHERE place_id=?");
                ps.setLong(1, gpsData_id);
                ps.setLong(2, place_id);
            }
            ps.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{ 
            if (ps != null) { try { ps.close(); } catch (SQLException ex) {}}  
        }
            
    }

    
    /*
     * when receive address of gpsData or a Place
     * action: insert into DB: address
     * if the address belong to gpsData then gspData_id_in >= 0 else < 0
     * if the address belong to a place then place_id_in >= 0 else < 0
     * if the address belong to bouth place and gpsData then place_id_in >= 0 and gpsData_ind_in >= 0
     * if both gpsData_id_in and place_id_in < 0 then do nothing
     * in: distance: distance from position specified by gpsData to address position specify by address lat and longitude
     * if distance < 0 -> not insert to db
     * 
     */
    public static void receiveAddress(GoogleApiDataTypes.Address address, long gpsData_id_in, long place_id_in, int distance){
        Statement st = null;
        try {
            String sqlCommand = buildSqlCommandAddress(address, gpsData_id_in, place_id_in, distance);
            st = Declare.mCon.createStatement();
            st.executeUpdate(sqlCommand);
        } catch (SQLException ex) {
            Logger.getLogger(DataReceiver.class.getName()).log(Level.SEVERE, null, ex);
        } finally{
            if ( st != null ){ try {  st.close(); } catch (SQLException ex) {} }
        }
    }
    
    /*
    * build sql command to insert google address into data base: table d_address
    * if gpsData_id_in < 0 -> no gpsData_id in insert command
    * if place_id_in < 0 -> no place_id in insert command
    * gpsData_id_in and place_id_in not allow to be null (<=0) at the same time (will get null returned)
    * if distance < 0 then not put distance to db
    */

    public static String buildSqlCommandAddress(GoogleApiDataTypes.Address address, long gpsData_id_in, long place_id_in, int distance_in){
        // must have at least of of id
        if ( (gpsData_id_in <= 0) && (place_id_in <= 0) ){
            return null;
        }
        
            String distance = "";
            String distance_v = "";
            
            if ( distance_in >= 0 ){ // not put distance in
                distance = "distance, ";
                distance_v = distance_in + ", ";
            } 
                
            String formatted_address = address.formatted_address != null ? "formatted_address, " : "" ;
            String formatted_address_v = address.formatted_address !=null ? "'" + address.formatted_address + "'" + ", ": "";

            String street_number  = "";
            String street_number_v = "";

            String route = "";
            String route_v = "";

            String locality = "";
            String locality_v = "";

            String administrative_area_level_1 = "";
            String administrative_area_level_1_v = "";

            String country = "";
            String country_v = "";

            String postal_code = "";
            String postal_code_v = "";

            String gpsData_id = "";
            String gpsData_id_v = "";

            String place_id = "";
            String place_id_v = "";

            GoogleApiDataTypes.Address_components address_component = null;
            
            // if has address components
            if ( (address.address_components != null) && (!address.address_components.isEmpty()) ){
            // get every address component
                for ( Iterator<GoogleApiDataTypes.Address_components> it = address.address_components.iterator(); it.hasNext(); ){
                    address_component = it.next();

                    // check type of the component
                    for ( String type : address_component.types){
                        // if have street number or street address
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_number.toString()) || 
                                type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.street_address.toString()) ){
                            // just because if too long
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){
                                street_number = "street_number, ";
                                street_number_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }

                        // if have route
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.route.toString()) ){
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){                            
                                route = "route, ";
                                route_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }

                        // if have locality
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.locality.toString()) ){
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){                            
                                locality = "locality, ";
                                locality_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }

                        // if have administrative_area_level_1
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.administrative_area_level_1.toString()) ){
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){                            
                                administrative_area_level_1 = "administrative_area_level_1, ";
                                administrative_area_level_1_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }

                        // if have country
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.country.toString()) ){
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){                            
                                country = "country, ";
                                country_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }

                        // if have postal_code
                        if ( type.equalsIgnoreCase(GoogleApiDataTypes.Address_Component_Types.postal_code.toString()) ){
                            if( (address_component.short_name != null) && (address_component.short_name.trim().length()>0) ){                            
                                postal_code = "postal_code, ";
                                postal_code_v = "'" + address_component.short_name + "'" + ", ";
                                break;
                            }
                        }
                    }
                }
            }
                
            String sqlCommand = "INSERT INTO d_address(";
            String columnsCommand = distance + formatted_address + street_number + route + locality + administrative_area_level_1 + country + postal_code;
            String valuesCommand = "VALUES(";
            valuesCommand += distance_v + formatted_address_v + street_number_v + route_v + locality_v + administrative_area_level_1_v + country_v + postal_code_v;


            // if have address then store
            if ( columnsCommand == null ){ return null; }
            if ( columnsCommand.trim().length() < 1 ){ return null; }

            // check insert gpsData_id and place_id or not
            // initial with both are null
            if ( gpsData_id_in > 0 ){
                gpsData_id = "gpsData_id";
                gpsData_id_v = "" + gpsData_id_in;

                if ( place_id_in > 0 ){
                    place_id = ", place_id";
                    place_id_v = ", " + place_id_in;
                }
            } else {
                if ( place_id_in > 0 ){
                    place_id = "place_id";
                    place_id_v = "" + place_id_in;
                }
            }

            columnsCommand += gpsData_id + place_id + ") ";
            valuesCommand += gpsData_id_v + place_id_v + ")";
            sqlCommand += columnsCommand + valuesCommand + ";";

            return sqlCommand;
    }

    
}
